Pro Power Tips 1.0A (c) 1992 Scanlon Enterprises 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴 DATA BASE POWER TIPS 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴 When you use a data base system, no matter which one, you need to create records, make reports and present data, in a concise and timely manner. Pro Power Tips, Database section, will guide you in these and other Database matters. Enhance Your dBase Logic Field Usage Tired of explaining what T and F stand for in DBase III Plus Reports? If your DBase reports often display the contents of logical fields, you'll produce more readable output by using DBase's IFF() function with the Active expression to substitute words for the logical values. Suppose you have a file called Vendors with a logical field called Active, containing T & F in a report column, instead of the Active Expression itself, use "IFF(ACTIVE,'YES','NO'". You can also use "IFF(ACTIVE, 'Active','Inactive'". Add Printer Codes for Better-Looking dBase Output You can spruce up dBase III plus reports by using printer control codes to produce compressed type, underlining, boldface and other print styles. Just use the following commands. ".SET DEVICE TO PRINT" ".@ PROW(),PCOL() SAY CHR(15)" ".SET DEVICE TO SCREEN" In the example above, "@ PROW(),PCOL() SAY" sends the control code "CHR(15)" (compressed type for Epson and IBM compatibles), to the printer at its current location. Substitute other control codes (which of course must be specific to your printer) to effect other print styles. The commands can be entered at the dot prompt, saved in their own command file or used within other command files. Paradox Shortcut, Pick Your Own Editor If you don't like the Paradox Script Editor, you can assign another ASCII text editor, such as Brief or Word, to run in Paradox. First be sure the text editors directory is referenced by the system PATH statement. From Paradox's main menu, select Script Play and type "CUSTOM" and press . Now answer the prompt about your monitor, and from a subsequent menu, choose Pal Editor. Type the name of the executable file that invokes your text editor, and follow it with a space and an asterisk. The asterisk, indicates that you want whatever script file is loaded when you invoke the editor. Also, you can put an exclamation point before the editors file name to give the editor more memory, and/or you can add another space and two more asterisks to end the line if your editor can accept a line number that jumps you to that position in the script. To exit, press , select Return Do-It, Do-It, save the CFG file to either the harddisk or Network. Paradox will close. The next time you load Paradox, Script Play will summon your favorite editor. dBase & Foxbase do Multiline Reporting Still stuck with an old dBase III Plus or Foxbase report writer? It may seem impossible to build a multiline report, but it's NOT. Here's the trick. Placing a semicolon within the column contents will wrap the column to the next line. This lets you load a multiline group of expressions, such as a three line address block, into a single column. To do this, enter the column contents, such as the following, into a single column. TRIM(FNAME) + " " + TRIM(LNAME) + ";" + ADDRESS + ";" + TRIM(CITY) + " " + STATE + " " + ZIP The above should be entered continuously. Set the column width no narrower that the widest possible combination of data appearing between the semicolons, otherwise, you'll get unintended word wrap effects. Unfortunately, there's no easy way to box and address. For that you'll have to upgrade. From a Table to a Form A Browse screen table view shows you many records at once, while an Edit screen form gives you a clearer view of one record. dBase III Plus, offers no way to toggle easily between these views, but you can create such a toggle by programming. Here's how to do it with the least amount of code : X = .F. DO WHILE .NOT. X BROWSE EDIT @21,37 SAY "Exit?" GET X READ ENDDO RETURN This example assumes that your data file is already open. The program starts a loop, that brings up the Browse screen, (dBase's table view). Here, you can scroll through your data and even edit it. When you exit Browse, by pressing or W the Edit screen appears, showing the record that was highlighted when you exited Browse. When you exit Edit, with , you'll be asked if you want to exit the program. If you respond with "Y" or "T", the program terminates. If you answer "N" or "F", the program resumes looping, and brings you back to the Browse screen. Making a Form Your Own When you view data in Paradox, acts as a hot key for going between the table and form views. By default, the form displayed with the hot key is a simple vertical listing of your table's fields. But Paradox lets you redesign this form to make it easier to read. For instance, you can place CITY, STATE & ZIP fields on a single line. To change the form display, select Forms Change from the MAIN menu. Enter the name of your table when prompted, and press to select the highlighted "F" from the list of forms that can be changed. (Other forms are listed by number. If the table has no other forms, only the "F" will be displayed.) Enter a description for the form, or press to keep the default, STANDARD FORM. That boring Standard Form now appears. Pressing brings up a menu of options for changing the look and, for that matter, the functionality of the form. For instance, you can use the Area Move command to move a field to another part of the screen, or the Border Place command to draw a box. Explore the menus to see what else is available. Once you are satisfied with your new form, press to save your work and exit. The next time you press from a table view, the current record will pop up using your new and improved form. Copy a Calculated Field in Paradox When you're designing a report, with a number of similar, complex, calculated fields, it's handy to copy a formula from one field to another. Unfortunately, Paradox, has no commands for doing this. You can get around this shortcoming, by creating an instant Script macro when you first enter the formula. While designing a report, before creating the first calculated field, press to start keystroke recording to an Instant Script. Then press and select Field Place Calculated. Next type in your formula, and press to end recording. When you want to place a similar calculated field in another location, simply press to play the Instant Script. You will soon be prompted to place the field. Press F, edit the formula, press , and place the field as prompted. Multi-Config for Q&A Changes made at Q&A's Utilities Set Global Defaults screen are saved in a file called QA.CFG. If you want to have alternative, reusable configurations, save one configuration before creating another, and copy this file to another name, such as QA2.CFG. To use QA2.CFG, copy it back to QA.CFG. This copy back and forth can be automated via a batch file. Don't forget to save the original QA.CFG, by copying it to a temporary file, such as QA.TMP, before you copy the QA2.CFG to QA.CFG. EXAMPLE: COPY the current QA.CFG file to QA1.CFG, you have now cloned the current config information. Now, enter Q&A's Utilities Set Global Defaults Screen, and set up your second Q&A configuration. EXIT Q&A and copy the new QA.CFG to QA2.CFG. Now, make two batch files, one called QA1.BAT the other QA2.BAT. The first line in each batch file will copy the correct QA config file. For QA1.BAT, this should be "COPY QA1.CFG QA.CFG", and for QA2.BAT, this should be "COPY QA2.CFG QA.CFG". Getting the Right Record With DBase III Plus Set Relation, which links two data bases by a common key, is one of the most powerful DBase III Plus commands. However, Set Relation has one undocumented quirk that can produce undesirable results. When DBase searches the child data base for the first key that matches the parent data base's key, the matching record may be a deleted one, even if Set Delete is on. To avoid this problem, ensure that the child data base contains no deleted records before you create the relation by issuing a Pack command. Duplicating Paradox Entries When working with Paradox 3.0+, it is often necessary to duplicate the contents of a field from one record to the next. To do this, use the Paradox "Ditto" key, which copies the value from the same field of the previous record. The "Ditto" key is the combined keystroke -D. This key combination is listed in the index of both the Paradox Introduction and the User's Guide manual. Effective Data Base Reports Effective data base reports turn data into information, but if your readers can't tell what they're looking at, the report is nothing but a dead tree! An informative title and clear column headings go a long way toward telling a reports story. Use two-part report headlines. For the title, carefully, choose descriptive words commonly employed in your business which never change. Next, build a modifiable heading, which identifies the data set on the particular report. This is especially important if you use the same report form (or layout) for more than one type of report. Suppose, you use the same layout to generate two reports on customers, but select the listings by different criteria. One report might show all of your customers, while the other might include only those who haven't bought anything this year. Using the same report form, not only saves you time, but if you use a layout consistently, your co-workers will know, for instance, that the last purchase date is always in the fifth column. But, if both reports have the same headline, you'll have some confused and angry co-workers. Everyone will be much happier if you name these reports something like "Customers" and "Inactive Customers". Most data managers let you supply report headings at run time. Sorted Details: The heading can do double duty, by telling your readers what order the records are in. Most people, assume that a report is sorted by, or printed in the order of, its left-hand column. It's therefore best to design a report form with the sorted column (be it NAME, ZIP or something else), on the left. This is NOT always possible, especially if you're using the same report form for a number of reports. The solution, specify the sort order in your heading, as in "Inactive Customers (No Purchase in 1991)". At the head of the Column: After the report heading, the column headings are the best clues to what's on the report. The easiest (but not necessarily the smartest) thing to do is simply accept default in many report writers and use the field names provided. If the fields have simple names, like, "Phone", that's no problem, but a column headed "DTPOST" will confuse the boss. Therefore, when the field name is a code or abbreviation, spell it out in the column heading. For instance, "Date Posted" is much easier to understand at a glance, that "DTPOST". If your description column heading is wider that its column, split it into two lines. You can do this with virtually all report writers. Once you've spelled out your column headings, format then for easy reading. Parallel justification is important. A left justified or centered heading will look confusing over a column of right justified numbers. AS a general rule, you should left justify character column headings, center those above dates and long descriptions and right justify heading over numbers. Other column heading design tips: Use uppercase and lower case letters. They're easier to read than all uppercase or lower case. Put a line below each column heading, stretching the full width of the column. If you've put some thought into your report heading and column headings, the people who read your reports will be happier. In the long career run, this will make you a happy camper. Naming Reports As You Print Them How would you go about printing, from a data base of all your customers, a report of just those customers added since last July? Assuming your report form is called CUSTLIST.FRM and your date field is called "First_Sale", the obvious answer is to use the command "REPORT FORM CustList TO PRINT FOR First_Sale >= C TO D("07/01/90")". One extra step will give your report a title that accurately reflects the data in the new report. The dBase REPORT command, has a little used HEADINGS option, that lets you add a title at print time. If you change the command to "REPORT FORM CustList TO PRINT FOR First_Sale >= C TO D("07/01/90") HEADING "Customers Added Since July 1, 1990", the resulting report will be much clearer to your co-workers. Reporting From a Paradox Query Paradox offers no direct way to print a report of only some of the records in a table. If you need a list of customers added since last July, for instance, you can't use your Customer table's Report R to print it. You can create an Ask query, that lists only those customers, then use the Tools Copy Report command to copy your report form from the Customer to the Answer table. Printing Answer's report rather than the Customer's will give you the selection you want. Even then, the reports' title won't state which customers are listed, and Paradox 3.0+ has no way to change a report title when you print it. So, before printing, the report, you'll have to use the Report Change command to change the name manually. Do this after you've copied the report to the Answer table, and be sure to change the name of the copy, not the original. If you have Paradox 3.5 is easier. When creating or changing the report, put a calculated field in the title's place, using a variable name as the expression. From then on, changing the contents of the variable will change the title of the report. MiniScript is an easy way to do this. Section finished. Be sure to order your THREE BONUS DISKS which expand this software package with vital tools, updates and additional tutorial material for computer users! Send $24.95 to Scanlon Enterprises, Department TIP, 38354 17th St. E., Palmdale, CA 93550. Bonus disks shipped promptly! Modifications, custom program versions, Site and LAN licenses of this package for business or corporate use are possible, contact the author. This software is shareware - an honor system which means TRY BEFORE YOU BUY. Press escape key to return to menu.